from sqlalchemy.inspection import inspect
from PySide6.QtWidgets import QWidget, QVBoxLayout, QTableWidget, QAbstractItemView, QHeaderView, QTableWidgetItem, QToolButton, QMenu, QMessageBox
from PySide6.QtGui import QAction, QBrush, QColor
from sqlalchemy import create_engine, MetaData
from sqlalchemy import text
import os
from sqlalchemy.ext.declarative import declarative_base

from BatRunWoker import Worker


class DbWidget(QWidget):

    def __init__(self, main_ui):
        super().__init__()

        self.setWindowTitle("PostgreSQL 数据库信息")
        layout = QVBoxLayout(self)
        self.base_dir = main_ui.base_dir
        self.logger = main_ui.logger
        self.config = main_ui.config
        self.table_widget = QTableWidget(self)
        self.table_widget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table_widget.setRowCount(1)
        self.table_widget.setColumnCount(7)
        self.table_widget.setEditTriggers(
            QAbstractItemView.EditTrigger.NoEditTriggers)
        self.table_widget.setHorizontalHeaderLabels(
            ["地址", "端口", "用户名", "密码", "数据库", "连接状态", "操作"]
        )

        self.table_widget.setItem(
            0, 0, QTableWidgetItem(self.config.get("db", "host")))
        self.table_widget.setItem(
            0, 1, QTableWidgetItem(self.config.get("db", "port")))
        self.table_widget.setItem(
            0, 2, QTableWidgetItem(self.config.get("db", "user")))
        self.table_widget.setItem(0, 3, QTableWidgetItem(
            self.config.get("db", "password")))
        self.table_widget.setItem(0, 4, QTableWidgetItem(
            self.config.get("db", "dbname")))

        # 菜单工具按钮
        tool_button_menu = QToolButton(self)
        self.table_widget.setCellWidget(0, 6, tool_button_menu)
        tool_button_menu.setText("操作")
        tool_button_menu.setAutoRaise(True)

        # 以下是为tool_button_menu添加menu信息。
        menu = QMenu(tool_button_menu)

        ini_action = QAction("打开配置文件", menu)
        ini_action.triggered.connect(
            lambda: os.startfile(os.path.join(self.base_dir, "config.ini"))
        )
        ini_action.setData("IniAction")
        menu.addAction(ini_action)
        open_action = QAction("打开数据库客户端", menu)
        open_action.triggered.connect(self.openhedisql)
        open_action.setData("OpenAction")
        menu.addAction(open_action)

        menu.addSeparator()
        Refresh_action = QAction("刷新", menu)
        menu.addAction(Refresh_action)
        Refresh_action.setData("IniAction")
        Refresh_action.triggered.connect(self.refresh_state)
        new_action = QAction("创建数据库", menu)
        new_action.triggered.connect(self.createdb)
        new_action.setData("NewAction")
        menu.addAction(new_action)

        deltable_action = QAction("删除表", menu)
        deltable_action.setData("deltable_action")
        deltable_action.triggered.connect(self.drop_tables)
        menu.addAction(deltable_action)

        del_action = QAction("删除数据库", menu)
        del_action.setData("del_action")
        # menu.addAction(del_action)

        menu.addSeparator()
        #    添加子菜单
        sub_menu = QMenu(menu)
        menu.addMenu(sub_menu)

        sub_menu.setTitle("备份")
        bk_schema_action = QAction("备份表结构", sub_menu)
        bk_schema_action.triggered.connect(self.backup_schema)

        bk_schema_action.setData("schema_action")
        sub_menu.addAction(bk_schema_action)

        bk_data_action = QAction("备份数据", sub_menu)
        bk_schema_action.setData("bk_data_action")
        # sub_menu.addAction(bk_data_action)

        restore_action = QAction("还原", menu)
        restore_action.triggered.connect(self.restore)

        restore_action.setData("restore_action")
        menu.addAction(restore_action)

        tool_button_menu.setMenu(menu)
        tool_button_menu.setPopupMode(QToolButton.InstantPopup)

        layout.addWidget(self.table_widget)
        self.setLayout(layout)
        self.refresh_state()

    def backup_schema(self):
        try:
            self.bk_worker = Worker(
                bat_file=r'db_backup.bat',
                logger=self.logger,
            )
            self.bk_worker.finished.connect(
                lambda: self.logger.log_info(f"完成"))
            self.bk_worker.start()
        except Exception as ex:
            self.logger.log_error(f"失败：{ex}")

    def restore(self):
        try:
            self.rst_worker = Worker(
                bat_file=r'db_init.bat',
                logger=self.logger,
            )
            self.rst_worker.finished.connect(
                lambda: self.logger.log_info(f"完成"))
            self.rst_worker.start()
        except Exception as ex:
            self.logger.log_error(f"失败：{ex}")

    def openhedisql(self):
        try:
            path = self.config.get('db', 'heidisql')
            absolute_path = os.path.abspath(path)
            self.logger.log_info(f"路径{absolute_path}")
            os.startfile(absolute_path)
            
        except Exception as ex:
            self.logger.log_error(f"失败：{ex}")

    def open_ini(self, str_dir):
        os.startfile(str_dir)

    def drop_tables(self):
        reply = QMessageBox.warning(
            self,
            "确认",
            "此过程不可撤销，确定要删除所有表吗?",
            QMessageBox.Yes | QMessageBox.No,
            QMessageBox.No,
        )
        if reply == QMessageBox.Yes:
            user = self.config.get("db", "user")
            password = self.config.get("db", "password")
            host = self.config.get("db", "host")
            port = self.config.get("db", "port")
            dbname = self.config.get("db", "dbname")
            conn = None
            try:
                engine = create_engine(
                    f"postgresql://{user}:{password}@{host}:{port}/{dbname}",
                    isolation_level="AUTOCOMMIT",
                )
                conn = engine.connect()
                metadata = MetaData()
                metadata.reflect(bind=engine)

                # 获取所有表格对象
                tables = metadata.sorted_tables
                table_len = len(tables)
                if table_len == 0:
                    self.logger.log_info(f"{dbname}表为空")
                    return
                # 删除所有表格
                count = 0
                for table in tables:
                    table.drop(engine)
                    count = count + 1
                    self.logger.log_info(f"{table.name}删除成功")
                self.logger.log_info(f"查询到{table_len}个数据表，成功删除{count}个数据表")

                query = text(
                    "SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public';"
                )
                result = conn.execute(query).fetchall()
                self.logger.log_info(f"剩余表:{result}")

            except Exception as ex:
                self.logger.log_error(ex)
            finally:
                if conn is not None:
                    conn.close()
                    self.logger.log_info("conn close")
        else:
            self.logger.log_info(f"取消操作")

    def createdb(self):
        user = self.config.get("db", "user")
        password = self.config.get("db", "password")
        host = self.config.get("db", "host")
        port = self.config.get("db", "port")
        dbname = self.config.get("db", "dbname")
        conn = None
        try:
            engine = create_engine(
                f"postgresql://{user}:{password}@{host}:{port}/postgres",
                isolation_level="AUTOCOMMIT",
            )
            conn = engine.connect()
            query = text(
                "SELECT datname FROM pg_database WHERE datistemplate = false;")
            result = conn.execute(query).fetchall()
            database_names = [d[0] for d in result]
            if dbname in database_names:
                self.logger.log_info(f"Database {dbname} already exists.")
            else:
                try:
                    conn.execute(
                        text(f"CREATE DATABASE {dbname}  owner {user} ENCODING 'utf-8';"))
                    self.logger.log_info(
                        f"Database {dbname} created successfully with UTF-8 encoding."
                    )
                except Exception as e:
                    self.logger.log_info(f"Error creating database: {e}")
        except Exception as ex:
            self.logger.log_error(ex)
        finally:
            if conn is not None:
                conn.close()

    def refresh_state(self):
        self.logger.log_info(f"当前路径{ os.getcwd()}")
        user = self.config.get("db", "user")
        password = self.config.get("db", "password")
        host = self.config.get("db", "host")
        port = self.config.get("db", "port")
        dbname = self.config.get("db", "dbname")
        result = False
        conn = None
        try:
            engine = create_engine(
                f"postgresql://{user}:{password}@{host}:{port}/{dbname}"
            )
            conn = engine.connect()
            query = text("SELECT version();")
            result = conn.execute(query)
            for i in result:
                result = True
                self.logger.log_info(f"连接成功:{i}")
                break
        except Exception as ex:
            self.logger.log_error(ex)
        finally:
            if conn is not None:
                conn.close()
        if result is True:
            item = QTableWidgetItem("正常")
            item.setBackground(QBrush(QColor(0, 255, 0)))
            self.table_widget.setItem(0, 5, item)
        else:
            item = QTableWidgetItem("错误")
            item.setBackground(QBrush(QColor(255, 0, 0)))
            self.table_widget.setItem(0, 5, item)


# if __name__ == "__main__":
#     app = QApplication(sys.argv)
#     widget = DbWidget()
#     widget.resize(800, 600)
#     widget.show()
#     sys.exit(app.exec())
